package com.tedu.boot21.controller;

import com.tedu.boot21.entity.Emp;
import com.tedu.boot21.utils.DBUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;

@Controller
public class EmpCortroller {
    @RequestMapping("/add")

    public void a(Emp emp, HttpServletResponse response) throws IOException {
        System.out.println("emp = " + emp);
//        获取链接
        try (Connection conn = DBUtils.getConn();) {
            String sql = "insert into myemp values(null,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, emp.getName());
            ps.setInt(2, emp.getSal());
            ps.setString(3, emp.getJob());
            ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        response.sendRedirect("/select");
    }


    @RequestMapping("/select")
    @ResponseBody
    public String b(Emp emp) {

        ArrayList<Emp> list = new ArrayList<>();

        try (Connection conn = DBUtils.getConn()) {
            String sql = "select *from myemp";
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);
//            遍历结果
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int sal = rs.getInt(3);
                String job = rs.getString(4);
//                把上面4个封装到emp对象中,并把emp对象装进list集合中
                Emp e = new Emp(id, name, sal, job);
                list.add(e);

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
//       此时得到从数据库查询到的所有员工信息
        String html = "<table border=1>";
        html += "<caption>员工列表</caption>";
        html += "<tr><th>id</th><th>名字</th><th>工资</th><th>工作</th><th>操作</th></tr>";

//        遍历集合  往table标签添加一行行数据
        for (Emp e : list) {
            html += "<tr>";
            html += "<td>" + e.getId() + "</td>";
            html += "<td>" + e.getName() + "</td>";
            html += "<td>" + e.getSal() + "</td>";
            html += "<td>" + e.getJob() + "</td>";
//            在请求地址的后面加上?,在?后面写上传递的参数 把id传递过去
            html += "<td><a href='/delete?id=" + e.getId() + "'>删除</td>";
            html += "</tr>";
        }
        html += "</table>";
        return html;//把拼接号的html标签返回给客户端
    }


    @RequestMapping("/delete")

    public void delete(int id, HttpServletResponse response) throws IOException {
        System.out.println("id = " + id);
//    获取链接
        try (Connection conn = DBUtils.getConn()) {
            String sql = "delete from myemp where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
//    重定向到指定页面  抛出异常
        response.sendRedirect("/select");
    }


    @RequestMapping("/update")
    public void update(Emp emp, HttpServletResponse response) throws IOException {
        try (Connection conn = DBUtils.getConn();) {
            String sql = "update myemp set name=?,sal=?,job=? where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, emp.getName());
            ps.setInt(2, emp.getSal());
            ps.setString(3, emp.getJob());
            ps.setInt(4, emp.getId());
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        response.sendRedirect("/select");
    }


}
